/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.FuncionarioMODEL;
import org.postgresql.util.PSQLException;

/**
 *
 * @author Welisson
 */
public class FuncionarioDAO extends GenericDAO {

    private static final String INSERT = "INSERT INTO funcionario( "
            + "log_cep, fun_nome, fun_cpf, fun_rg, fun_nasc, fun_sexo, fun_senha, fun_num)\n"
            + "    VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
    private static final String DELETE = "DELETE FROM funcionario WHERE fun_id = ?;";
    private static final String UPDATE = "UPDATE funcionario\n"
            + "SET log_cep=?, fun_nome=?, fun_cpf=?, fun_rg=?, fun_nasc=?, fun_sexo=?, fun_senha=?, fun_num=?, func_id = ? WHERE fun_id = ?;";
    private static final String SELECT = "SELECT * FROM funcionario ORDER BY fun_id ASC;";

    private static final String INSERT_TELEFONE = "INSERT INTO telefones_funcs(fun_id, tel_func_num, tel_func_tipo) VALUES (?, ?, ?);";
    private static final String DELETE_TELEFONE = "DELETE FROM telefones_funcs WHERE fun_id = ?;";
    private static final String SELECT_TELEFONE = "SELECT fun_id, tel_func_num, tel_func_tipo FROM telefones_funcs WHERE fun_id = ?;";

    private static final String INSERT_EMAIL = "INSERT INTO emails_funcs(fun_id, ema_func_email) VALUES (?, ?);";
    private static final String DELETE_EMAIL = "DELETE FROM emails_funcs WHERE fun_id = ?;";
    private static final String SELECT_EMAIL = "SELECT fun_id, ema_func_email FROM emails_funcs WHERE fun_id = ? ;";

    public int insert(FuncionarioMODEL func) throws SQLException {

        PreparedStatement ps = conn.prepareStatement(INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
        conn.setAutoCommit(false);
        ResultSet rs;
        int id = -1;
        try {
            ps.setObject(1, func.getCep());
            ps.setObject(2, func.getNome());
            ps.setObject(3, func.getCpf());
            ps.setObject(4, func.getRg());
            ps.setObject(5, new java.sql.Date(func.getNasc().getTime()));
            ps.setObject(6, func.getSexo());
            ps.setObject(7, func.getSenha());
            ps.setObject(8, func.getNum());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();

            
            while (rs.next()) {
                id = rs.getInt(1);
            }

            if (!func.getTelCel().isEmpty()) {
                ps = conn.prepareStatement(INSERT_TELEFONE);
                ps.setObject(1, id);
                ps.setObject(2, func.getTelCel());
                ps.setObject(3, "CEL");

                ps.execute();
            }
            if (!func.getTelRes().isEmpty()) {
                ps = conn.prepareStatement(INSERT_TELEFONE);
                ps.setObject(1, id);
                ps.setObject(2, func.getTelRes());
                ps.setObject(3, "RES");

                ps.execute();
            }
            if (!func.getTelCom().isEmpty()) {
                ps = conn.prepareStatement(INSERT_TELEFONE);
                ps.setObject(1, id);
                ps.setObject(2, func.getTelCom());
                ps.setObject(3, "COM");

                ps.execute();
            }

            List<String> email = func.getEmail();
            for (int i = 0; i < email.size(); i++) {
                ps = conn.prepareStatement(INSERT_EMAIL);
                ps.setObject(1, id);
                ps.setObject(2, email.get(i));

                ps.execute();
            }
            conn.commit();
        } catch (PSQLException e) {
            e.printStackTrace(System.out);
            conn.rollback();
            id = -1;
        }
        conn.setAutoCommit(true);
        ps.close();
        conn.close();
        return id;
    }

    public boolean update(FuncionarioMODEL func) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        conn.setAutoCommit(false);
        boolean ok = false;
        try {
            ps.setObject(1, func.getCep());
            ps.setObject(2, func.getNome());
            ps.setObject(3, func.getCpf());
            ps.setObject(4, func.getRg());
            ps.setObject(5, new java.sql.Date(func.getNasc().getTime()));
            ps.setObject(6, func.getSexo());
            ps.setObject(7, func.getSenha());
            ps.setObject(8, func.getNum());
            ps.setObject(9, func.getId());
            ps.execute();

            ps = conn.prepareStatement("DELETE FROM telefones_funcs WHERE fun_id = ?;");
            ps.setObject(1, func.getId());
            ps.execute();

            if (!func.getTelCel().replace("(", "").replace("-", "").replace(")", "").trim().equals("")) {

                ps = conn.prepareStatement(INSERT_TELEFONE);
                ps.setObject(1, func.getId());
                ps.setObject(2, func.getTelCel());
                ps.setObject(3, "CEL");

                ps.execute();
            }

            if (!func.getTelRes().replace("(", "").replace("-", "").replace(")", "").trim().equals("")) {

                ps = conn.prepareStatement(INSERT_TELEFONE);
                ps.setObject(1, func.getId());
                ps.setObject(2, func.getTelRes());
                ps.setObject(3, "RES");

                ps.execute();
            }
            if (!func.getTelCom().replace("(", "").replace("-", "").replace(")", "").trim().equals("")) {
                ps = conn.prepareStatement(INSERT_TELEFONE);
                ps.setObject(1, func.getId());
                ps.setObject(2, func.getTelCom());
                ps.setObject(3, "COM");

                ps.execute();
            }

            ps = conn.prepareStatement("DELETE FROM emails_funcs WHERE fun_id = ?;");
            ps.setObject(1, func.getId());
            ps.execute();

            List<String> email = func.getEmail();
            for (int i = 0; i < email.size(); i++) {
                ps = conn.prepareStatement(INSERT_EMAIL);
                ps.setObject(1, func.getId());
                ps.setObject(2, email.get(i));

                ps.execute();
            }

            conn.commit();
            ok = true;
        } catch (PSQLException e) {
            e.printStackTrace(System.out);
            conn.rollback();
        }
        conn.setAutoCommit(true);
        ps.close();
        conn.close();
        return ok;
    }

    public boolean delete(int id) throws SQLException {
        PreparedStatement ps = conn.prepareStatement(DELETE_TELEFONE);
        boolean ok = false;
        conn.setAutoCommit(false);

        try {

            ps.setObject(1, id);
            ps.execute();

            ps = conn.prepareStatement(DELETE_EMAIL);
            ps.setObject(1, id);
            ps.execute();

            ps = conn.prepareStatement(DELETE);
            ps.setObject(1, id);
            ps.execute();

            conn.commit();
            ok = true;
        } catch (PSQLException e) {
            e.printStackTrace(System.out);
            conn.rollback();
        }
        conn.setAutoCommit(true);
        ps.close();
        conn.close();
        return ok;
    }

    public List<FuncionarioMODEL> select() throws SQLException {
        List<FuncionarioMODEL> funcs = new ArrayList<FuncionarioMODEL>();

        PreparedStatement ps = conn.prepareStatement(SELECT);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            FuncionarioMODEL func = new FuncionarioMODEL();

            List<String> emails = new ArrayList<String>();

            int id = rs.getInt("fun_id");
            func.setId(id);
            func.setNome(rs.getString("fun_nome"));
            func.setCpf(rs.getString("fun_cpf"));
            func.setRg(rs.getString("fun_rg"));
            func.setNasc(rs.getDate("fun_nasc"));
            func.setSexo(rs.getString("fun_sexo"));
            func.setSenha(rs.getString("fun_senha"));
            func.setCep(rs.getInt("log_cep"));
            func.setNum(rs.getInt("fun_num"));

            ps = conn.prepareStatement(SELECT_TELEFONE);
            ps.setObject(1, id);
            ResultSet rsTel = ps.executeQuery();
            while (rsTel.next()) {
                if ("CEL".equals(rsTel.getString("tel_func_tipo"))) {
                    func.setTelCel(rsTel.getString("tel_func_num"));
                } else if ("RES".equals(rsTel.getString("tel_func_tipo"))) {
                    func.setTelRes(rsTel.getString("tel_func_num"));
                } else if ("COM".equals(rsTel.getString("tel_func_tipo"))) {
                    func.setTelCom(rsTel.getString("tel_func_num"));
                }

            }

            rsTel.close();
//            System.out.println("Qtd: "+tel.size());

            ps = conn.prepareStatement(SELECT_EMAIL);
            ps.setObject(1, id);
            ResultSet rsEmail = ps.executeQuery();
            while (rsEmail.next()) {

                emails.add(rsEmail.getString("ema_func_email"));

            }
            func.setEmail(emails);
            rsEmail.close();

            funcs.add(func);
        }
        ps.close();
        rs.close();

        conn.close();

        return funcs;
    }

    public boolean findByCPF(String cpf) throws SQLException {
        String selectCPF = "SELECT * FROM funcionario WHERE fun_cpf = ?";
        PreparedStatement ps = conn.prepareStatement(selectCPF);
        ps.setObject(1, cpf);
        ResultSet rs = ps.executeQuery();
        
        while(rs.next()){
            return true;
        }
        return false;
    }
    
    public boolean utilizandoFuncao(int funcao) throws SQLException{
        String selectFuncao = "SELECT * FROM funcionarios WHERE func_id = ?";
        PreparedStatement ps = conn.prepareStatement(selectFuncao);
        ps.setObject(1, funcao);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            return true;
        }
        return false;
    }
}
